# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.engine import Engine
from sqlalchemy.orm import sessionmaker
from urllib.parse import quote_plus

import logging

logger = logging.getLogger(__name__)

username = 'chench'
password = 'hn@cch'
host = 'localhost'
port = 3306
db = 'sqlalchemy_db'

# SQLAlchemy>=1.4.16:密码含义特殊字符"!@#$%^&*()"是需要转义
password = quote_plus(password)
db_url = f'mysql+pymysql://{username}:{password}@{host}:{port}/{db}'

engine: Engine = create_engine(
    db_url,
    pool_size=2,
    pool_recycle=30,
    pool_timeout=30,
    pool_pre_ping=True,
    max_overflow=0,
    echo_pool=True,
    echo=True,
)


def handle_result(result_proxy):
    # ResultProxy

    for row_proxy in result_proxy:
        # row_proxy: RowProxy
        for column_name in row_proxy.keys():
            logger.warning(f'{column_name}:{row_proxy[column_name]}')
            pass
        pass


def engine_execute(statement):
    result_proxy = engine.execute(statement)
    handle_result(result_proxy)


def py_conn_execute(statement):
    conn = engine.connect()
    result_proxy = conn.execute(statement)
    handle_result(result_proxy)
    conn.close()


def py_sess_execute(statement):
    Session = sessionmaker(bind=engine, autoflush=False)  # noqa
    sess = Session()
    result_proxy = sess.execute(statement)
    handle_result(result_proxy)
    sess.close()


if __name__ == '__main__':
    sql_script = 'select version();'
    engine_execute(sql_script)
    py_conn_execute(sql_script)
    py_sess_execute(sql_script)
